﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySkiPatrolEntity;

namespace MySkiPatrolSQL
{
    public static class MembershipSQL
    {
        public static string Login(WebAccess data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_member where username = '" + data.Username + "' ");
            return sql.ToString(); 
        }

        public static string GetMembers(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            
            sql.Append("SELECT a.[MemberID] ");
            sql.Append(",a.[FirstName] ");
            sql.Append(",a.[LastName] ");
            sql.Append(",a.[Username] ");
            sql.Append(",a.[Password] ");
            sql.Append(",a.[NSPNumber] ");
            sql.Append(",a.[DateDeleted] ");
            sql.Append("FROM [" + data.DataTransaction.PatrolPrefix + "_Member] a ");
            sql.Append("order by a.FirstName ");

            return sql.ToString();
        }

        public static string GetMember(Member data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_member where memberid = '" + data.ID + "'");
            return sql.ToString();
        }

        public static string AddMember(Member data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO " + data.DataTransaction.PatrolPrefix + "_Member ");
            sql.Append("([MemberID] ");
            sql.Append(",[FirstName] ");
            sql.Append(",[LastName] ");
            sql.Append(",[Username] ");
            sql.Append(",[Password] ");
            sql.Append(",[NSPNumber])");
            //sql.Append(",[DateDeleted]) ");
            sql.Append("VALUES ");
            sql.Append("('" + data.ID + "' ");
            sql.Append(",'" + data.FirstName + "' ");
            sql.Append(",'" + data.LastName + "' ");
            sql.Append(",'" + data.Username + "' ");
            sql.Append(",'" + data.Password + "' ");
            sql.Append(",'" + data.NSPNumber + "')");
            //sql.Append(",'" + data.DateDeleted + "') ");

            return sql.ToString();
        }

        public static string UpdateMember(Member data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update _member set ");
            sql.Append("firstname = '" + data.FirstName + "', ");
            sql.Append("middlename = '" + data.MiddleName + "', ");
            sql.Append("lastname = '" + data.LastName + "', ");
            sql.Append("NSPnumber = '" + data.NSPNumber + "', ");
            sql.Append("prefix = '" + data.Prefix + "', ");
            sql.Append("suffix = '" + data.Suffix + "', ");
            sql.Append("username = '" + data.Username + "' ");
            sql.Append("where memberid = '" + data.ID +"'");
            return sql.ToString();
        }

        public static string ChangeMemberPassword(Member data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update _member set ");
            sql.Append("[password] = '" + data.Password + "' ");
            sql.Append("where memberid = '" + data.ID + "'");
            return sql.ToString();
        }

        public static string DeleteMember(Member data)
        {
            //something to think about...do we do a logic or physical delete?
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + data.DataTransaction.PatrolPrefix + "_member set DateDeleted = '" + data.DataTransaction.ProcessDateTime + "' where memberid = '" + data.ID + "'");
            return sql.ToString();
        }

        #region Groups
        public static string GetMemberGroups(Member data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select distinct a.* from " + data.DataTransaction.PatrolPrefix + "_Group a join " + data.DataTransaction.PatrolPrefix + "_groupmember b on a.groupid = b.groupid where b.memberid = '" + data.ID + "'");
            return sql.ToString();
        }
        #endregion

        #region Roles
        public static string GetMemberRoles(Member data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select distinct a.* from " + data.DataTransaction.PatrolPrefix + "_role a join " + data.DataTransaction.PatrolPrefix + "_rolemember b on a.roleid = b.roleid where b.memberid = '" + data.ID + "'");
            return sql.ToString();
        }
        #endregion

        #region IDCards
        public static string GetMemberIDCards(Member data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select a.* from " + data.DataTransaction.PatrolPrefix + "_memberidcards a where a.memberid = '" + data.ID + "'");
            return sql.ToString();
        }

        public static string GetMemberIDCard(IDCard data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select a.* from " + data.DataTransaction.PatrolPrefix + "_memberidcards a where a.id = '" + data.CardID + "'");
            return sql.ToString();
        }

        public static string AddMemberIDCard(IDCard data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into " + data.DataTransaction.PatrolPrefix + "_memberidcards (");
            sql.Append("id, memberid");
            if(data.Expires != DateTimeOffset.MinValue) sql.Append(",expires");
            sql.Append(") values (");
            sql.Append("'" + data.CardID + "','" + data.MemberID + "'");
            if(data.Expires != DateTimeOffset.MinValue) sql.Append(",'" + data.Expires + "'");
            sql.Append(")");
            return sql.ToString();
        }

        public static string DeleteMemberIDCard(IDCard data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + data.DataTransaction.PatrolPrefix + "_memberidcards set datedeleted = '" + data.DateDeleted + "' where ID = '" + data.CardID + "'");
            return sql.ToString();
        }



        #endregion
    }
}
